package it.uniroma3.dia.db;




import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Types;
/** 
* @author Daniele D'Andrea
* @author Antonio Gallo
*/
public class DBPopulator {

	private DBDatasource database;
	
	public static void main(String[] args) {
		try{
			DBPopulator dbPopulator = new DBPopulator();
			dbPopulator.fillDB();
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

	public void fillDB() throws Exception{

		System.out.println("init FillDB");

		this.database = new DBDatasource();
		
		fillMoviesTable();
		fillTagsTable();
		fillMovieCountriesTable();
		fillMovieDirectorsTable();
		fillMovieGenresTable();
		fillMovieLocationsTable();
		fillMovieTagsTable();
		fillUserTaggedMoviesTable();
		fillUserRatedMoviesTable();
		fillMovieActorsTable();

		System.out.println("finish FillDB");
	}

	private void fillMoviesTable(){
		try
		{
			// Apertura connessione 
			Connection connection = database.getConnection();

			String s;
			BufferedReader reader = new BufferedReader(new FileReader(System.getProperty("user.dir")+"/dataSet/movies.dat") );

			reader.readLine();

			while( (s = reader.readLine()) != null )
			{
				String[] parameters=s.split("\\t");
				// Creazione oggetto Statement
				PreparedStatement statement = connection.prepareStatement(DBQueryConstant.INSERT_MOVIES);

				statement.setInt(1,Integer.parseInt(parameters[0]));

				if(!parameters[1].equals("\\N"))
					statement.setString(2, parameters[1]);
				else statement.setNull(2, Types.VARCHAR);
				if(!parameters[2].equals("\\N"))
					statement.setString(3, parameters[2]);
				else statement.setNull(3, Types.VARCHAR);
				if(!parameters[3].equals("\\N"))
					statement.setString(4, parameters[3]);
				else statement.setNull(4, Types.VARCHAR);
				if(!parameters[4].equals("\\N"))
					statement.setString(5, parameters[4]);
				else statement.setNull(5, Types.VARCHAR);
				if(!parameters[5].equals("\\N"))
					statement.setInt(6,Integer.parseInt(parameters[5]));
				else statement.setNull(6, Types.INTEGER);
				if(!parameters[6].equals("\\N"))
					statement.setString(7, parameters[6]);
				else statement.setNull(7, Types.VARCHAR);
				if(!parameters[7].equals("\\N"))
					statement.setString(8, parameters[7]);
				else statement.setNull(8, Types.VARCHAR);
				if(!parameters[8].equals("\\N"))
					statement.setInt(9,Integer.parseInt(parameters[8]));
				else statement.setNull(9, Types.INTEGER);
				if(!parameters[9].equals("\\N"))
					statement.setInt(10,Integer.parseInt(parameters[9]));
				else statement.setNull(10, Types.INTEGER);
				if(!parameters[10].equals("\\N"))
					statement.setInt(11,Integer.parseInt(parameters[10]));
				else statement.setNull(11, Types.INTEGER);
				if(!parameters[11].equals("\\N"))
					statement.setInt(12,Integer.parseInt(parameters[11]));
				else statement.setNull(12, Types.INTEGER);
				if(!parameters[12].equals("\\N"))
					statement.setFloat(13,Float.parseFloat(parameters[12]));
				else statement.setNull(13, Types.FLOAT);
				if(!parameters[13].equals("\\N"))
					statement.setInt(14,Integer.parseInt(parameters[13]));
				else statement.setNull(14, Types.INTEGER);
				if(!parameters[14].equals("\\N"))
					statement.setInt(15,Integer.parseInt(parameters[14]));
				else statement.setNull(15, Types.INTEGER);
				if(!parameters[15].equals("\\N"))
					statement.setInt(16,Integer.parseInt(parameters[15]));
				else statement.setNull(16, Types.INTEGER);
				if(!parameters[16].equals("\\N"))
					statement.setInt(17,Integer.parseInt(parameters[16]));
				else statement.setNull(17, Types.INTEGER);
				if(!parameters[17].equals("\\N"))
					statement.setFloat(18,Float.parseFloat(parameters[17]));
				else statement.setNull(18, Types.FLOAT);
				if(!parameters[18].equals("\\N"))
					statement.setInt(19,Integer.parseInt(parameters[18]));
				else statement.setNull(19, Types.INTEGER);
				if(!parameters[19].equals("\\N"))
					statement.setInt(20,Integer.parseInt(parameters[19]));
				else statement.setNull(20, Types.INTEGER);
				if(!parameters[20].equals("\\N"))
					statement.setString(21, parameters[20]);
				else statement.setNull(21, Types.VARCHAR);
				// Esecuzione di un comando sul DB
				statement.executeUpdate(); 

				statement.close();
			}
			reader.close();
			// Chiusura della connessione

			connection.close();

			System.out.println("movies filled.");
		}
		catch (Exception ex){
			ex.printStackTrace();
		}
	}
	private void fillTagsTable(){
		try
		{
			// Apertura connessione 
			Connection connection = database.getConnection();

			String s;
			BufferedReader reader = new BufferedReader(new FileReader(System.getProperty("user.dir")+"/dataSet/tags.dat") );

			reader.readLine();

			while( (s = reader.readLine()) != null )
			{
				String[] parameters=s.split("\\t");
				// Creazione oggetto Statement
				PreparedStatement statement = connection.prepareStatement(DBQueryConstant.INSERT_TAGS);

				statement.setInt(1,Integer.parseInt(parameters[0]));
				statement.setString(2, parameters[1]);

				// Esecuzione di un comando sul DB
				statement.executeUpdate(); 
				statement.close();
			}
			reader.close();
			// Chiusura della connessione
			connection.close();

			System.out.println("tags filled.");
		}
		catch (Exception ex){
			ex.printStackTrace();
		}
	}
	private void fillMovieActorsTable(){
		try
		{
			// Apertura connessione 
			Connection connection = database.getConnection();

			String s;
			BufferedReader reader = new BufferedReader(new FileReader(System.getProperty("user.dir")+"/dataSet/movie_actors.dat") );

			reader.readLine();

			while( (s = reader.readLine()) != null )
			{
				String[] parameters=s.split("\\t");
				// Creazione oggetto Statement
				PreparedStatement statement = connection.prepareStatement(DBQueryConstant.INSERT_MOVIE_ACTORS);

				statement.setInt(1,Integer.parseInt(parameters[0]));
				statement.setString(2, parameters[1]);
				statement.setString(3,parameters[2]);
				statement.setInt(4, Integer.parseInt(parameters[3]));

				// Esecuzione di un comando sul DB
				statement.executeUpdate(); 
				statement.close();
			}
			reader.close();
			// Chiusura della connessione
			connection.close();

			System.out.println("movie_actors filled.");
		}
		catch (Exception ex){
			ex.printStackTrace();
		}
	}
	private void fillMovieCountriesTable(){
		try
		{
			// Apertura connessione 
			Connection connection = database.getConnection();

			String s;
			BufferedReader reader = new BufferedReader(new FileReader(System.getProperty("user.dir")+"/dataSet/movie_countries.dat") );

			reader.readLine();

			while( (s = reader.readLine()) != null )
			{
				String[] parameters=s.split("\\t");
				// Creazione oggetto Statement
				PreparedStatement statement = connection.prepareStatement(DBQueryConstant.INSERT_MOVIE_COUNTRIES);

				statement.setInt(1,Integer.parseInt(parameters[0]));
				if(parameters.length>=2)
					statement.setString(2, parameters[1]);
				else
					statement.setNull(2, Types.VARCHAR);

				// Esecuzione di un comando sul DB
				statement.executeUpdate(); 
				statement.close();
			}
			reader.close();
			// Chiusura della connessione
			connection.close();

			System.out.println("movie_countries filled.");
		}
		catch (Exception ex){
			ex.printStackTrace();
		}
	}
	private void fillMovieDirectorsTable(){
		try
		{
			// Apertura connessione 
			Connection connection = database.getConnection();
			
			String s;
			BufferedReader reader = new BufferedReader(new FileReader(System.getProperty("user.dir")+"/dataSet/movie_directors.dat") );

			reader.readLine();

			while( (s = reader.readLine()) != null )
			{
				String[] parameters=s.split("\\t");
				// Creazione oggetto Statement
				PreparedStatement statement = connection.prepareStatement(DBQueryConstant.INSERT_MOVIE_DIRECTORS);

				statement.setInt(1,Integer.parseInt(parameters[0]));
				statement.setString(2, parameters[1]);
				statement.setString(3, parameters[2]);

				// Esecuzione di un comando sul DB
				statement.executeUpdate(); 
				statement.close();
			}
			reader.close();
			// Chiusura della connessione
			connection.close();

			System.out.println("movie_directors filled.");
		}
		catch (Exception ex){
			ex.printStackTrace();
		}
	}
	private void fillMovieGenresTable(){
		try
		{
			// Apertura connessione 
			Connection connection = database.getConnection();
			
			String s;
			BufferedReader reader = new BufferedReader(new FileReader(System.getProperty("user.dir")+"/dataSet/movie_genres.dat") );

			reader.readLine();

			while( (s = reader.readLine()) != null )
			{
				String[] parameters=s.split("\\t");
				// Creazione oggetto Statement
				PreparedStatement statement = connection.prepareStatement(DBQueryConstant.INSERT_MOVIE_GENRES);

				statement.setInt(1,Integer.parseInt(parameters[0]));
				statement.setString(2, parameters[1]);

				// Esecuzione di un comando sul DB
				statement.executeUpdate(); 
				statement.close();
			}
			reader.close();
			// Chiusura della connessione
			connection.close();

			System.out.println("movie_genres filled.");
		}
		catch (Exception ex){
			ex.printStackTrace();
		}
	}
	private void fillMovieLocationsTable(){
		try
		{
			// Apertura connessione 
			Connection connection = database.getConnection();
			
			String s;
			BufferedReader reader = new BufferedReader(new FileReader(System.getProperty("user.dir")+"/dataSet/movie_locations.dat") );

			reader.readLine();

			while( (s = reader.readLine()) != null )
			{
				String[] parameters=s.split("\\t");
				// Creazione oggetto Statement
				PreparedStatement statement = connection.prepareStatement(DBQueryConstant.INSERT_MOVIE_LOCATIONS);

				statement.setInt(1,Integer.parseInt(parameters[0]));
				if(parameters.length>=2)
					statement.setString(2, parameters[1]);
				else
					statement.setNull(2, Types.VARCHAR);
				if(parameters.length>=3)
					statement.setString(3, parameters[2]);
				else
					statement.setNull(3, Types.VARCHAR);
				if(parameters.length>=4)
					statement.setString(4, parameters[3]);
				else
					statement.setNull(4, Types.VARCHAR);
				if(parameters.length>=5)
					statement.setString(5, parameters[4]);
				else
					statement.setNull(5, Types.VARCHAR);

				// Esecuzione di un comando sul DB
				statement.executeUpdate(); 
				statement.close();
			}
			reader.close();
			// Chiusura della connessione
			connection.close();

			System.out.println("movie_locations filled.");
		}
		catch (Exception ex){
			ex.printStackTrace();
		}
	}
	
	private void fillMovieTagsTable(){
		try
		{
			// Apertura connessione 
			Connection connection = database.getConnection();
			
			String s;
			BufferedReader reader = new BufferedReader(new FileReader(System.getProperty("user.dir")+"/dataSet/movie_tags.dat") );

			reader.readLine();

			while( (s = reader.readLine()) != null )
			{
				String[] parameters=s.split("\\t");
				// Creazione oggetto Statement
				PreparedStatement statement = connection.prepareStatement(DBQueryConstant.INSERT_MOVIE_TAGS);

				statement.setInt(1,Integer.parseInt(parameters[0]));
				statement.setInt(2,Integer.parseInt(parameters[1]));
				statement.setInt(3,Integer.parseInt(parameters[2]));

				// Esecuzione di un comando sul DB
				statement.executeUpdate(); 
				statement.close();
			}
			reader.close();
			// Chiusura della connessione
			connection.close();

			System.out.println("movie_tags filled.");
		}
		catch (Exception ex){
			ex.printStackTrace();
		}
	}
	private void fillUserRatedMoviesTable(){
		try
		{
			// Apertura connessione 
			Connection connection = database.getConnection();
			
			String s;
			BufferedReader reader = new BufferedReader(new FileReader(System.getProperty("user.dir")+"/dataSet/user_ratedmovies.dat") );

			reader.readLine();

			while( (s = reader.readLine()) != null )
			{
				String[] parameters=s.split("\\t");
				// Creazione oggetto Statement
				PreparedStatement statement = connection.prepareStatement(DBQueryConstant.INSERT_USER_RATEDMOVIES);
				
				statement.setInt(1,Integer.parseInt(parameters[0]));
				statement.setInt(2,Integer.parseInt(parameters[1]));
				statement.setFloat(3,Float.parseFloat(parameters[2]));
				statement.setLong(4,Long.parseLong(parameters[3]));

				// Esecuzione di un comando sul DB
				statement.executeUpdate(); 
				statement.close();
			}
			reader.close();
			// Chiusura della connessione
			connection.close();

			System.out.println("movie_ratedmovies filled.");
		}
		catch (Exception ex){
			ex.printStackTrace();
		}
	}
	private void fillUserTaggedMoviesTable(){
		try
		{
			// Apertura connessione 
			Connection connection = database.getConnection();
			
			String s;
			BufferedReader reader = new BufferedReader(new FileReader(System.getProperty("user.dir")+"/dataSet/user_taggedmovies.dat") );

			reader.readLine();

			while( (s = reader.readLine()) != null )
			{
				String[] parameters=s.split("\\t");
				// Creazione oggetto Statement
				PreparedStatement statement = connection.prepareStatement(DBQueryConstant.INSERT_USER_TAGGEDMOVIES);

				
				statement.setInt(1,Integer.parseInt(parameters[0]));
				statement.setInt(2,Integer.parseInt(parameters[1]));
				statement.setInt(3,Integer.parseInt(parameters[2]));
				statement.setLong(4,Long.parseLong(parameters[3]));

				// Esecuzione di un comando sul DB
				statement.executeUpdate(); 
				statement.close();
			}
			reader.close();
			// Chiusura della connessione
			connection.close();

			System.out.println("movie_taggedmovies filled.");
		}
		catch (Exception ex){
			ex.printStackTrace();
		}
	}
}
